package at.fhj.itm10.ticketmaster.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import at.fhj.itm10.ticketmaster.models.Customer;

/**
 * Customer DAO.
 * 
 * @author Wolfgang Gaar
 */
public class CustomerDAO extends AbstractDAO {
	
	/**
	 * Must be an integer in the database.
	 */
	private final String PRIMARYKEY_NAME = "id";

	private final String SQL_INSERT = "INSERT INTO `customer` (`username`, `password`, `firstname`, `lastname`, `male`," + 
	" `title`, `email`, `birth_date`, `register_date`, `newsletter`, `organizer`, `activation_key`, `activated`, `fk_address_id`) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
	
	private final String SQL_UPDATE = "UPDATE `customer` SET `username` = ?, `password` = ?, `firstname` = ?, `lastname` = ?, `male` = ?," + 
	" `title` = ?, `email` = ?, `birth_date` = ?, `register_date` = ?, `newsletter` = ?, `organizer` = ?, `activation_key` = ?, `activated` = ?," +
	" `fk_address_id` = ?  WHERE `id` = ?";
	
	private final String SQL_DELETE = "DELETE FROM `customer` WHERE `id` = ?";
	
	private final String SQL_FINDBYID = "SELECT * FROM `customer` WHERE `id` = ?";
	
	private final String SQL_FINDBYEMAIL = "SELECT * FROM `customer` WHERE `email` = ?";
	
	private final String SQL_FINDBYUSERNAME = "SELECT * FROM `customer` WHERE `username` = ?";
	
	private final String SQL_FINDALL = "SELECT * FROM `customer`";

	public CustomerDAO(Connection connection) throws DAOException {
		super(connection);
	}

	public void insert(Customer entity) throws DAOException {
		checkForValidEntity(entity);
		PreparedStatement statement = prepareStatement(SQL_INSERT);

		try {
			statement.setString(1, entity.getUserName());
			statement.setString(2, entity.getPassword());
			statement.setString(3, entity.getFirstName());
			statement.setString(4, entity.getLastName());
			statement.setBoolean(5, entity.isMale());
			statement.setString(6, entity.getTitle());
			statement.setString(7, entity.geteMail());
			statement.setString(8, convertDateToString(entity.getBirthDate()));
			statement.setString(9, convertDateToString(entity.getRegisterDate()));
			statement.setBoolean(10, entity.isNewsletter());
			statement.setBoolean(11, entity.isOrganizer());
			statement.setString(12, entity.getActivationKey());
			statement.setBoolean(13, entity.isActivated());
			statement.setLong(14, entity.getFkAddressId());
		} catch (Exception e) {
			throw new DAOException(e);
		}

		super.processInsert(statement, entity);
	}

	public void update(Customer entity) throws DAOException {
		checkForValidEntity(entity);
		PreparedStatement statement = prepareStatement(SQL_UPDATE);
		
		try {
			statement.setString(1, entity.getUserName());
			statement.setString(2, entity.getPassword());
			statement.setString(3, entity.getFirstName());
			statement.setString(4, entity.getLastName());
			statement.setBoolean(5, entity.isMale());
			statement.setString(6, entity.getTitle());
			statement.setString(7, entity.geteMail());
			statement.setString(8, convertDateToString(entity.getBirthDate()));
			statement.setString(9, convertDateToString(entity.getRegisterDate()));
			statement.setBoolean(10, entity.isNewsletter());
			statement.setBoolean(11, entity.isOrganizer());
			statement.setString(12, entity.getActivationKey());
			statement.setBoolean(13, entity.isActivated());
			statement.setLong(14, entity.getFkAddressId());
			statement.setLong(15, entity.getId());
		} catch (Exception e) {
			throw new DAOException(e);
		}

		super.processUpdate(statement, entity);
	}

	public void delete(Customer entity) throws DAOException {
		checkForValidEntity(entity);

		// Entity gets deleted by its id automatically, you just need
		// one placeholder in your DELETE query
		super.doDelete(prepareStatement(SQL_DELETE), entity);
	}

	/**
	 * Find one by id.
	 * 
	 * @param id
	 * @return Customer
	 * @throws DAOException
	 */
	public Customer findById(long id) throws DAOException {
		Customer entity = (Customer) retrieveEntity(id);
		if (entity != null) {
			return entity;
		}

		// Do not set anything for the find-one query, as this is
		// done in super.doFindOne()
		return (Customer) super.doFindOne(id, prepareStatement(SQL_FINDBYID));
	}

	/**
	 * Find all by email.
	 * 
	 * @param email
	 * @return List<Customer>
	 * @throws DAOException
	 */
	public List<Customer> findByEMail(String email) throws DAOException {
		if (email == null) {
			throw new NullPointerException("email is null");
		}
		
		PreparedStatement statement = prepareStatement(SQL_FINDBYEMAIL);

		try {
			statement.setString(1, email);
		} catch (SQLException e) {
			throw new DAOException(e);
		}

		return super.doFindMore(PRIMARYKEY_NAME, statement);
	}

	/**
	 * Find all by username
	 * 
	 * @param username
	 * @return List<Customer>
	 * @throws DAOException
	 */
	public List<Customer> findByUsername(String username) throws DAOException {
		if (username == null) {
			throw new NullPointerException("username is null");
		}
		
		PreparedStatement statement = prepareStatement(SQL_FINDBYUSERNAME);

		try {
			statement.setString(1, username);
		} catch (SQLException e) {
			throw new DAOException(e);
		}

		return super.doFindMore(PRIMARYKEY_NAME, statement);
	}

	/**
	 * Find all.
	 * 
	 * @return List<Customer>
	 * @throws DAOException
	 */
	public List<Customer> findAll() throws DAOException {
		return super.doFindMore(PRIMARYKEY_NAME, prepareStatement(SQL_FINDALL));
	}

	/**
	 * {@inheritDoc}
	 */
	protected Customer fillEntityByResult(ResultSet resultSet) throws DAOException {
		Customer customer = new Customer();
		
		try {
			customer.setId(resultSet.getLong(PRIMARYKEY_NAME));
			customer.setUserName(resultSet.getString("username"));
			customer.setPassword(resultSet.getString("password"));
			customer.setFirstName(resultSet.getString("firstname"));
			customer.setLastName(resultSet.getString("lastname"));
			customer.setMale(resultSet.getBoolean("male"));
			customer.setTitle(resultSet.getString("title"));
			customer.seteMail(resultSet.getString("email"));
			customer.setBirthDate(convertStringToDate(resultSet.getString("birth_date")));
			customer.setRegisterDate(convertStringToDate(resultSet.getString("register_date")));
			customer.setNewsletter(resultSet.getBoolean("newsletter"));
			customer.setOrganizer(resultSet.getBoolean("organizer"));
			customer.setActivationKey(resultSet.getString("activation_key"));
			customer.setActivated(resultSet.getBoolean("activated"));
			customer.setFkAddressId(resultSet.getLong("fk_address_id"));
		} catch (Exception e) {
			throw new DAOException(e);
		}

		return customer;
	}
}
